﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wwtfly.BBS.BackModels.Models.ExpertNews;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Wwtfly.DBUtility;
using System.Data.SqlClient;
using Wwtfly.BBS.Framework.Common;

namespace Wwtfly.BBS.BackDAL.ExpertNews
{
   public partial class ExpertNewsDAL
    {
        public const string PM_EXPERTNEWS_SELECT_BY_ID = " SELECT [Id],[CreateTime],[UserId],[PlayId],[PlayQiNo],[Extitle],[Keyword],[Exmiaoshu],[Uptime],[Endtime],[Scheme],[ColourMoney],[ClickSum],[CheckMsg],[ExperRemark]" +
                                             " FROM [pm_expertNews]" +
                                             " WHERE (" +
                                             " [Id] = @Id)";

        public const string PM_ExpertNewsShows = "SELECT top 20 [Id],[CreateTime],[UserId],[PlayId],[PlayQiNo],[Extitle],[Keyword],[Exmiaoshu],[Uptime],[Endtime],[Scheme],[ColourMoney],[ClickSum],[CheckMsg],[ExperRemark]" +
                                            " FROM [pm_expertNews] where [ColourMoney]>0 and [Endtime]>getdate() and [CheckMsg]='1' order by [CreateTime]";


        public const string PM_ExpertNewsShows2 = "SELECT top 20 [Id],[CreateTime],[UserId],[PlayId],[PlayQiNo],[Extitle],[Keyword],[Exmiaoshu],[Uptime],[Endtime],[Scheme],[ColourMoney],[ClickSum],[CheckMsg],[ExperRemark]" +
                                     " FROM [pm_expertNews] where [Endtime]<getdate() and [CheckMsg]='1' order by [CreateTime] desc";
        public const string PM_EXPERTNEWS_INSERT = " INSERT INTO [pm_expertNews] (" +
                                            "[CreateTime],[UserId],[PlayId],[PlayQiNo],[Extitle],[Keyword],[Exmiaoshu],[Uptime],[Endtime],[Scheme],[ColourMoney],[ClickSum],[CheckMsg],[ExperRemark]" +
                                        ") VALUES (" +
                                            "@CreateTime,@UserId,@PlayId,@PlayQiNo,@Extitle,@Keyword,@Exmiaoshu,@Uptime,@Endtime,@Scheme,@ColourMoney,@ClickSum,@CheckMsg,@ExperRemark" +
                                        ");select @Id = @@Identity;";


        public const string PM_EXPERTNEWS_UPDATE = " UPDATE [pm_expertNews] SET " +
                                            "[Extitle]=@Extitle,[Keyword]=@Keyword,[Exmiaoshu]=@Exmiaoshu,[Uptime]=@Uptime,[Endtime]=@Endtime,[Scheme]=@Scheme,[ColourMoney]=@ColourMoney,[ClickSum]=@ClickSum,[CheckMsg]=@CheckMsg,[ExperRemark]=@ExperRemark" +
                                        " WHERE " +
                                            " [Id] = @Id";



        public const string PM_EXPERTNEWS_DELETE = "DELETE FROM [pm_expertNews]" +
                                        " WHERE " +
                                            " [Id] = @Id";
       /// <summary>
       /// 查看明细专家专题对象
       /// </summary>
        public const string pm_expertnewsshowslook = "proc_userlookexpertNewshows_2";
       /// <summary>
       /// 执行查看专家信息的操作
       /// </summary>
        public const string PM_ExpertlookNews = "proc_userlookexpertNews";

       /// <summary>
       /// 获取专家专题分页
       /// </summary>
        public const string proc_ExpertNewsInfoPageList = "proc_ExpertNewsInfoPageList_Select";
         /// <summary>
        /// 获取专家专题分页
        /// </summary>
        /// <param name="pageParams">分页参数</param>
        /// <param name="searchModel">新闻搜索 实体类</param>
        /// <returns>分页后的结果</returns>
        public static PagedList<ExpertNewsShowsModel> GetExpertNewsShowseList(PageParams pageParams, ExpertNewsShowsSearchModel searchModel)
        {
            IList<ExpertNewsShowsModel> list = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(proc_ExpertNewsInfoPageList);
            DBParamsUtility.CreatePageParamsOfNotOutTotalCount(pageParams, db, dbCommand);
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@Extitle", SqlDbType = SqlDbType.NVarChar, Value = searchModel.Extitle });
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@PlayId", SqlDbType = SqlDbType.Int, Value = searchModel.PlayId });
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                list = Emit.ConvertToList<ExpertNewsShowsModel>(dr);
                pageParams.SetTotalCount((int)DBParamsUtility.GetTotalCount<ExpertNewsShowsModel>(list));
            }
            return list.ToPagedList<ExpertNewsShowsModel>(pageParams);

        }
       /// <summary>
       /// 用户查看专家信息查询
       /// </summary>
       /// <param name="Id"></param>
       /// <returns></returns>
        public static ExpertNewsShowsModel GetInfoshows(int userid,int expertID,out int sult)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(pm_expertnewsshowslook);
            db.AddInParameter(dbCommand, "@userId", DbType.Int32, userid);
            db.AddInParameter(dbCommand, "@expertId", DbType.Int32, expertID);
            db.AddOutParameter(dbCommand, "@result", DbType.Int32,4);
            ExpertNewsShowsModel expertnewsshow = null;
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
               
                expertnewsshow= Emit.ConvertTo<ExpertNewsShowsModel>(dr);
            }
            sult = (int)dbCommand.Parameters["@result"].Value;
            return expertnewsshow;
        }
       /// <summary>
        /// 查看专家信息的操作
       /// </summary>
       /// <param name="userid">用户ID</param>
       /// <param name="expertID">专家专题ID</param>
       /// <param name="UserColourMoney">用户自身金额</param>
       /// <param name="caibi">访问的彩币数量</param>
       /// <returns></returns>
        public static int ExpertLookNewsExcut(int userid, int expertID, int UserColourMoney, int caibi)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(PM_ExpertlookNews);
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@userId", SqlDbType = SqlDbType.Int, Value = userid });
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@expertId", SqlDbType = SqlDbType.Int, Value = expertID });
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@UserColourMoney", SqlDbType = SqlDbType.Int, Value = UserColourMoney });
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@caibi", SqlDbType = SqlDbType.Int, Value = caibi });
            db.AddOutParameter(dbCommand, "@sult", DbType.Int32, 4);
            db.ExecuteNonQuery(dbCommand);
            return (int)dbCommand.Parameters["@sult"].Value;
        }
       /// <summary>
       /// 获取首页的前20条预测专题
       /// </summary>
       /// <returns></returns>
        public static IList<ExpertNewsShowsModel> ExpertNewsShowsList(int ifchek)
        {
            string pmexpertsql = PM_ExpertNewsShows;
            if (ifchek == 1)
            {
                pmexpertsql = PM_ExpertNewsShows2;
            }
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(pmexpertsql);
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                return Emit.ConvertToList<ExpertNewsShowsModel>(dr);
            }
        }
        /// <summary>
        /// 根据Id获取指定的pmexpertNews
        /// </summary>
        /// <param name="Id">pm_expertNews主键</param>
        /// <returns></returns>
        public static ExpertNewsShowsModel GetInfo(Int32 Id)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_EXPERTNEWS_SELECT_BY_ID);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, Id);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                return Emit.ConvertTo<ExpertNewsShowsModel>(dr);
            }
        }

        /// <summary>
        /// 添加新的pmexpertNews
        /// </summary>
        /// <param name="pmexpertNews">pmexpertNews实体</param>
        /// <returns></returns>
        public static int Add(ExpertNewsShowsModel pmexpertNews)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_EXPERTNEWS_INSERT);

            db.AddOutParameter(dbCommand, "@Id", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, DBConvert.ToDBValue(pmexpertNews.CreateTime));
            db.AddInParameter(dbCommand, "@UserId", DbType.Int32, DBConvert.ToDBValue(pmexpertNews.UserId));
            db.AddInParameter(dbCommand, "@PlayId", DbType.Int32, DBConvert.ToDBValue(pmexpertNews.PlayId));
            db.AddInParameter(dbCommand, "@PlayQiNo", DbType.String, DBConvert.ToDBValue(pmexpertNews.PlayQiNo));
            db.AddInParameter(dbCommand, "@Extitle", DbType.String, DBConvert.ToDBValue(pmexpertNews.Extitle));
            db.AddInParameter(dbCommand, "@Keyword", DbType.String, DBConvert.ToDBValue(pmexpertNews.Keyword));
            db.AddInParameter(dbCommand, "@Exmiaoshu", DbType.String, DBConvert.ToDBValue(pmexpertNews.Exmiaoshu));
            db.AddInParameter(dbCommand, "@Uptime", DbType.DateTime, DBConvert.ToDBValue(pmexpertNews.Uptime));
            db.AddInParameter(dbCommand, "@Endtime", DbType.DateTime, DBConvert.ToDBValue(pmexpertNews.Endtime));
            db.AddInParameter(dbCommand, "@Scheme", DbType.String, DBConvert.ToDBValue(pmexpertNews.Scheme));
            db.AddInParameter(dbCommand, "@ColourMoney", DbType.Decimal, DBConvert.ToDBValue(pmexpertNews.ColourMoney));
            db.AddInParameter(dbCommand, "@ClickSum", DbType.Decimal, DBConvert.ToDBValue(pmexpertNews.ClickSum));
            db.AddInParameter(dbCommand, "@CheckMsg", DbType.String, DBConvert.ToDBValue(pmexpertNews.CheckMsg));
            db.AddInParameter(dbCommand, "@ExperRemark", DbType.String, DBConvert.ToDBValue(pmexpertNews.ExperRemark));


            int count = db.ExecuteNonQuery(dbCommand);

            if (count > 0)
                return (int)dbCommand.Parameters["@Id"].Value;
            return -1;
        }

        /// <summary>
        /// 修改WKF_WorkItemOfKit
        /// </summary>
        /// <param name="pmexpertNews">pmexpertNews实体</param>
        /// <returns></returns>
        public static bool Update(ExpertNewsShowsModel pmexpertNews)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_EXPERTNEWS_UPDATE);

            db.AddInParameter(dbCommand, "@Id", DbType.Int32, DBConvert.ToDBValue(pmexpertNews.Id));
            //db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, DBConvert.ToDBValue(pmexpertNews.CreateTime));
            //db.AddInParameter(dbCommand, "@UserId", DbType.Int32, DBConvert.ToDBValue(pmexpertNews.UserId));
            //db.AddInParameter(dbCommand, "@PlayId", DbType.Int32, DBConvert.ToDBValue(pmexpertNews.PlayId));
            //db.AddInParameter(dbCommand, "@PlayQiNo", DbType.String, DBConvert.ToDBValue(pmexpertNews.PlayQiNo));
            db.AddInParameter(dbCommand, "@Extitle", DbType.String, DBConvert.ToDBValue(pmexpertNews.Extitle));
            db.AddInParameter(dbCommand, "@Keyword", DbType.String, DBConvert.ToDBValue(pmexpertNews.Keyword));
            db.AddInParameter(dbCommand, "@Exmiaoshu", DbType.String, DBConvert.ToDBValue(pmexpertNews.Exmiaoshu));
            db.AddInParameter(dbCommand, "@Uptime", DbType.DateTime, DBConvert.ToDBValue(pmexpertNews.Uptime));
            db.AddInParameter(dbCommand, "@Endtime", DbType.DateTime, DBConvert.ToDBValue(pmexpertNews.Endtime));
            db.AddInParameter(dbCommand, "@Scheme", DbType.String, DBConvert.ToDBValue(pmexpertNews.Scheme));
            db.AddInParameter(dbCommand, "@ColourMoney", DbType.Decimal, DBConvert.ToDBValue(pmexpertNews.ColourMoney));
            db.AddInParameter(dbCommand, "@ClickSum", DbType.Decimal, DBConvert.ToDBValue(pmexpertNews.ClickSum));
            db.AddInParameter(dbCommand, "@CheckMsg", DbType.String, DBConvert.ToDBValue(pmexpertNews.CheckMsg));
            db.AddInParameter(dbCommand, "@ExperRemark", DbType.String, DBConvert.ToDBValue(pmexpertNews.ExperRemark));
            return db.ExecuteNonQuery(dbCommand) > 0;
        }

        /// <summary>
        /// 删除WKF_WorkItemOfKit 
        /// </summary>
        /// <param name="Id">pm_expertNews主键</param>
        /// <returns></returns>
        public static bool Delete(Int32 Id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_EXPERTNEWS_DELETE);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, Id);
            return db.ExecuteNonQuery(dbCommand) > 0;
        }

    }
}
